package com.example.preparedstatement.crud;

import com.example.connection.ConnectionTest;
import com.example.util.JDBCUtils;
import org.junit.Test;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Properties;

// 使用PreparedStatement来替换Statement，实现对数据表的增删改查操作
public class PreparedStatementUpdateTest {

    // 插入数据
    @Test
    public void testInsert() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            InputStream inputStream = ConnectionTest.class.getClassLoader()
                    .getResourceAsStream("jdbc.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            String url = properties.getProperty("url");
            String user = properties.getProperty("user");
            String password = properties.getProperty("password");
            String driverClass = properties.getProperty("driverClass");

            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, password);

            // 预编译SQL
            String sql = "INSERT INTO `customers`(`name`, `email`, `birth`) VALUES (?, ?, ?)";
            preparedStatement = connection.prepareStatement(sql);
            // 填充占位符
            preparedStatement.setString(1, "admin");
            preparedStatement.setString(2, "admin@qq.com");
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            preparedStatement.setDate(3, new Date(simpleDateFormat.parse("2019-10-10").getTime()));
            // 执行操作
            preparedStatement.execute();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 资源关闭
            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    // 修改数据
    @Test
    public void testUpdate() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JDBCUtils.getConnection();

            String sql = "UPDATE `customers` SET `name` = ? WHERE `id` = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "root");
            preparedStatement.setInt(2, 2);

            preparedStatement.execute();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

    // 通用的增删改操作
    public void update(String sql, Object... args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 获取连接
            connection = JDBCUtils.getConnection();
            // 编译SQL
            preparedStatement = connection.prepareStatement(sql);
            // 设置参数
            for (int i = 0; i < args.length; i++) {
                preparedStatement.setObject(i + 1, args[i]);
            }
            // 执行操作
            preparedStatement.execute();
        } catch (Exception exception) {
            exception.printStackTrace();
        } finally {
            // 释放资源
            JDBCUtils.closeResource(connection, preparedStatement);
        }
    }

    @Test
    public void testCommonUpdate() {
        // 删除数据
        String sql = "DELETE FROM `customers` WHERE `id` = ?";
        update(sql, 3);

        // 更新数据
        sql = "UPDATE `order` SET `order_name` = ? WHERE `order_id` = ?";
        update(sql, "DD", 2);
    }

}
